package com.magicalcoder.pigeon.mysql.manager.execute;

import com.magicalcoder.pigeon.mysql.manager.DbConnection;
import com.magicalcoder.pigeon.mysql.manager.orm.ORMap;
import com.mysql.cj.jdbc.exceptions.CommunicationsException;

import java.sql.*;
import java.util.List;

/**
 * Created by hzhedongyu on 2017/6/12.
 */
public class ExecuteSql implements Execute{

    private DbConnection dbConnection;

    public ExecuteSql(DbConnection dbConnection) {
        this.dbConnection = dbConnection;
    }

    @Override
    public void select1() {
        Connection connection = dbConnection.getConnection();
        ResultSet resultSet = null;
        Statement statement = null;
        try
        {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select 1;");
        }catch (CommunicationsException e){//失去连接
            dbConnection.getPigeonConnection().reConnect();
            e.printStackTrace();
            throw new RuntimeException("与数据库通信异常");
        }
        catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("SQLException");
        } finally {
            try {
                if(resultSet!=null){
                    resultSet.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            try {
                if(statement!=null){
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public <T> List<T> selectList(String sql, Class<T> clazz){
        Connection connection = dbConnection.getConnection();
        ResultSet resultSet = null;
        Statement statement = null;
        List<T> resultList = null;
        try
        {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            ORMap<T> orMap = new ORMap<T>();
            resultList = orMap.coverToList(resultSet,clazz);
        }catch (CommunicationsException e){//失去连接
            dbConnection.getPigeonConnection().reConnect();
            e.printStackTrace();
            throw new RuntimeException("与数据库通信异常");
        }
        catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("SQLException");
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            throw new RuntimeException("IllegalAccessException");
        } finally {
            try {
                if(resultSet!=null){
                    resultSet.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            try {
                if(statement!=null){
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultList;
    }

    @Override
    public void update(String sql, List<Object> params){
        Connection connection = dbConnection.getConnection();
        ResultSet resultSet = null;
        PreparedStatement ps = null;
        try
        {
            ps = connection.prepareStatement(sql);
            if(params!=null){
                for(int i=0;i<params.size();i++){
                    ps.setObject(i+1,params.get(i));
                }
            }
            int result = ps.executeUpdate();
            System.out.println(result);
        }catch (CommunicationsException e){//失去连接
            dbConnection.getPigeonConnection().reConnect();
            e.printStackTrace();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            try {
                if(resultSet!=null){
                    resultSet.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }

            try {
                if(ps!=null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    public void release(){
        dbConnection.getPigeonConnection().release();
    }

    //关于事务 虽然数据库连接池复用了 但是同一时刻必须保证只有一个线程持有连接池 否则就会串调 各种问题 连事务也无法保证
    public void begin(){
        try {
            dbConnection.getConnection().setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void commit(){
        try {
            dbConnection.getConnection().commit();//会自动恢复事务 autoCommit=true 所以不要手动恢复了
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void rollback(){
        try {
            dbConnection.getConnection().rollback();
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
